<?php

namespace org\util;

use think\Db;

class DbHelper {
    /**
     * 获取数据库表信息
     * @return mixed
     */
    static public function getTables($query='')
    {
        $db = config('database.database');
        $sql = "select 
                TABLE_NAME as table_name,
                ENGINE as engine,
                TABLE_ROWS as table_rows,
                TABLE_COLLATION as table_collation,
                TABLE_COMMENT as table_comment 
                from information_schema.TABLES 
                where information_schema.TABLES.TABLE_SCHEMA = '{$db}'";
        if(!empty($query)) $sql .= " and {$query}";
        $tables = Db::query($sql);
        return $tables;
    }
    /**
     * 获取表字段
     * @param $table
     * @return mixed
     */
    static public function getFields($table)
    {
        $db = config('database.database');
        $sql = "select 
                COLUMN_KEY as column_key,
                COLUMN_NAME as column_name,
                DATA_TYPE as data_type,
                COLUMN_TYPE as column_type,
                COLUMN_COMMENT as column_comment,
                COLUMN_DEFAULT as column_default,
                TABLE_NAME as table_name
                from information_schema.columns 
                where table_schema ='{$db}'  and table_name = '{$table}'";
        $fields = Db::query($sql);
        return $fields;
    }
    /**
     * 检测表是否存在
     * @param $table
     * @return bool
     */
    static public function checkTable($table)
    {
        $sql = "show tables like '{$table}'";
        $info = Db::query($sql);
        return empty($info) ? false : true;
    }

    /**
     * 检测字段是否存在，可以同时检测多个字段
     * @param $table
     * @param $fieldName 字段名用","隔开
     * @return bool
     */
    static public function checkField($table,$fieldName){
        $InFieldName = str_replace(',',"','",$fieldName);
        $db = config('database.database');
        $sql = "SELECT count(*) as count FROM
            information_schema. COLUMNS WHERE information_schema. COLUMNS .TABLE_SCHEMA = '{$db}'
            AND information_schema. COLUMNS .TABLE_NAME = '{$table}'
            AND information_schema. COLUMNS .COLUMN_NAME in ('{$InFieldName}')";
        $info = Db::query($sql);
        if(empty($info)) return false;
        if($info[0]['count']==count(explode(',',$fieldName))) return true;
        return false;
    }

    /**
     * 获取字段类型
     * @return array
     */
    static public function getFieldTypes(){
        return [
            ["id"=>"varchar","name"=>"varchar:变长字符串(0-255字节)",'default_length'=>'255'],
            ["id"=>"char","name"=>"char:定长字符串(0-255字节)",'default_length'=>'60'],
            ["id"=>"tinyint","name"=>"tinyint:小整数值(-128，127)",'default_length'=>'4'],
            ["id"=>"smallint","name"=>"smallint:大整数值(-32768，32767)",'default_length'=>'4'],
            ["id"=>"int","name"=>"int:大整数值(-2147483648，2147483647)",'default_length'=>'10'],
            ["id"=>"float","name"=>"float:浮点数值(-3.4E38~3.4E38)",'default_length'=>'10,2'],
            ["id"=>"decimal","name"=>"decimal:货币数值(128bit)",'default_length'=>'10,4'],
            ["id"=>"text","name"=>"text:长文本数据(0-65535字节)",'default_length'=>'0'],
            ["id"=>"mediumtext","name"=>"mediumtext:中等长度文本数据(0-16777215字节)",'default_length'=>'0'],
        ];
    }

    /**
     * 导出数据库文档
     * @return mixed
     */
    static public function export(){
        $tables = self::getTables();
        foreach($tables as $k=>$v){
            $tables[$k]['fields'] = self::getFields($v['table_name']);
        }
        return $tables;
    }
}